﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data;
namespace DAO
{
    public class KhachHangDAO
    {
        //them khach hang
        public int ThemKhachHang(KhachHangDTO kh)
        {
            try
            {
                string sql="insert into khachhang values('"+kh.Useid+"',N'"+kh.TenKH+"',N'"+kh.DiaChi+"',"+kh.MaTinh+",'"+kh.SoDT+"','"+kh.Email+"',"+kh.DiemTichLuy+","+kh.Tinhtrang+")";
                return Dataprovider.ExcuteNonQuery(sql);
            }
            catch
            {
                return 0;
            }
        }
        // cap nhat khach hang
        public int CapNhatKhachHang(KhachHangDTO kh)
        {
            try
            {
                string sql = "update khachhang set tenkh= N'" + kh.TenKH + "',diachi= N'" + kh.DiaChi + "',matinh= "+kh.MaTinh+",sdt='" + kh.SoDT + "',email='" + kh.Email + "',tinhtrang=" + kh.Tinhtrang +" where makh= "+kh.MaKH;
                return Dataprovider.ExcuteNonQuery(sql);
            }
            catch
            {
                return 0;
            }
        }
        //load danh sach khach hang
        public DataTable LoadDanhSachKhachHang()
        {
            try
            {
                string sql = "select * from khachhang kh, aspnet_Users u, aspnet_Membership m where kh.UserId = u.UserId and u.UserId = m.UserId";
                return Dataprovider.ExcuteQuery(sql);
            }
            catch
            {
                return null;
            }
        }
        //load thong tin khach hang
        public KhachHangDTO LoadThongTinKhachHang(string taikhoan)
        {
            try
            {
                KhachHangDTO kh = new KhachHangDTO();
                string sql = "select MaKH, TenKH, DiaChi, MaTinh, SDT, kh.Email, DiemTichLuy from khachhang kh, aspnet_Users u, aspnet_Membership m where kh.UserId = u.UserId and u.UserId = m.UserId and u.username = '" + taikhoan + "'";
                DataTable dt = Dataprovider.ExcuteQuery(sql);
                foreach(DataRow r in dt.Rows)
                {
                    kh.MaKH = int.Parse(r[0].ToString());
                    kh.TenKH = r[1].ToString();
                    kh.DiaChi = r[2].ToString();
                    kh.MaTinh = int.Parse(r[3].ToString());
                    kh.SoDT = r[4].ToString();
                    kh.Email = r[5].ToString();
                    kh.DiemTichLuy = int.Parse(r[6].ToString());
                }
                return kh;
            }
            catch
            {
                return null;
            }
        }
        //kiem tra tinh trang khach hang
        public int KiemTraTinhTrang(string taikhoan)
        {
            try
            {
                string sql = "select tinhtrang from KhachHang kh, aspnet_Users u where kh.UserId = u.UserId and UserName = '"+taikhoan+"'";
                return int.Parse(Dataprovider.ExcuteScalar(sql));
            }
            catch
            {
                return 0;
            }
        }
        //tim kiem nguoi dung
        public DataTable TimKiemKhachHang(string taikhoan)
        {
            try
            {
                string sql = "select MaKH, username, TenKH, DiaChi, MaTinh, SDT, kh.Email, tinhtrang from khachhang kh, aspnet_Users u, aspnet_Membership m where kh.UserId = u.UserId and u.UserId = m.UserId and username like '"+taikhoan+"'";
                return Dataprovider.ExcuteQuery(sql);
            }
            catch
            {
                return null;
            }
        }
    }
}
